package com.gitee.zzh.sqllineage.util;

import com.gitee.zzh.sqllineage.vo.TransformVO;

import java.util.*;

public class SqlStringUtils extends MyStringUtils {

    public static List<String> bracketSubStr(String text, String subString) {
        return bracketSubStr(text, subString, "(", ")");
    }

    private static String getTempTable(String sqlText) {
        String sqlTextTrim = sqlText.trim();
        String right = null;
        if (sqlTextTrim.startsWith("CREATE TABLE ")) {
            right = sqlTextTrim.substring("CREATE TABLE".length());
        }
        if (sqlTextTrim.startsWith("INSERT INTO ")) {
            right = sqlTextTrim.substring("INSERT INTO ".length());
        }
        if (sqlTextTrim.startsWith("UBDATE ")) {
            right = sqlTextTrim.substring("UPDATE ".length());
        }
        if (isBlank(right)) {
            return null;
        }
        return substringBySymbol(right.trim(), " ", ",", "(", ")");
    }

    public static Set<String> getTempTableList(String text) {
        Set<String> tempTableList = new HashSet<>();
        String[] textArr = text.split(";");
        for (String sqlText : textArr) {
            String tableName = getTempTable(sqlText);
            if (tableName != null) {
                tempTableList.add(tableName.trim());
            }
        }
        return tempTableList;
    }

    private static List<String> getUpdateSelectSqlText(String text, String tableTemp) {
        List<String> list = new ArrayList<>();
        if (!text.contains("UPDATE " + tableTemp + " ") && !text.contains("UPDATE " + tableTemp + ",")) {
            return list;
        }
        String[] textArr = text.split(";");
        for (String textPart : textArr) {
            String textTrim = textPart.trim();
            if (!textTrim.startsWith("UPDATE " + tableTemp + " ") && !textTrim.startsWith("UPDATE " + tableTemp + ",")) {
                continue;
            }
            if (textTrim.contains(" JOIN (SELICT ")) {
                if (!textTrim.substring(0, textTrim.indexOf(" JOIN (SELICT ")).contains(",")) {
                    list.add(tableTemp);
                }
            } else {
                if (textTrim.substring(0, textTrim.indexOf(" SET ")).contains(",")) {
                    list.add(tableTemp);
                }
            }
        }
        return list;
    }

    public static List<String> getInsertSqlText(String text, String tableTemp) {
        List<String> list = new ArrayList<>();
        if (!text.contains("INSERT INTO " + tableTemp + " ") && !text.contains("INSERT INTO " + tableTemp + "(")) {
            return list;
        }
        String[] textArr = text.split(";");
        for (String textPart : textArr) {
            String textTrim = textPart.trim();
            if ((textTrim.startsWith("INSERT INTO " + tableTemp + " ") || textTrim.startsWith("INSERT INTO " + tableTemp + "("))
                    && (textTrim.contains("(SELECT ") || textTrim.contains(" SELECT "))) {
                list.add(textTrim);
            }
        }
        return list;
    }

    public static String getCreateAsSqlText(String text, String tableTemp) {
        tableTemp = getFinalTableName(text, tableTemp);
        if(!text.contains("CREATE TABLE " + tableTemp + " AS ")) {
            return null;
        }
        if(text.indexOf("CREATE TABLE " + tableTemp + " AS ") < text.lastIndexOf("CREATE TABLE " + tableTemp + " AS ")) {
            System.err.println("有多个CREATE TABLE " + tableTemp + " AS ");
            return null;
        }
        String[] textArr = text.split(";");
        for (String textPart : textArr) {
            String textTrim = textPart.trim();
            if (textTrim.startsWith("CREATE TABLE " + tableTemp + " ") && textTrim.contains(" AS SELECT ")) {
                return textTrim;
            } else if (textTrim.startsWith("CREATE TABLE " + tableTemp + " LIKE ")) {
                String tableName1 = textTrim.substring(textTrim.lastIndexOf(" LIKE ") + " LIKE ".length()).trim();
                tableName1 = substringBySymbol(tableName1, " ");
                return getCreateAsSqlText(text, tableName1);
            }
        }
        return null;
    }

    public static Set<String[]> getSelectSqlText(String text, Set<String> tableNameTempList) {
        Set<String[]> sqlList = new HashSet<>();
        for (String tableNameTemp : tableNameTempList) {
            List<String> insertIntoSqlList = getInsertSqlText(text, tableNameTemp);
            if (!insertIntoSqlList.isEmpty()) {
                for (String s : insertIntoSqlList) {
                    if (s.contains("(SELECT ")) {
                        sqlList.add(new String[]{tableNameTemp, s});
                    }
                }
            }
            String createAsSql = getCreateAsSqlText(text, tableNameTemp);
            if (createAsSql != null) {
                if (createAsSql.contains("(SELECT ")) {
                    sqlList.add(new String[]{tableNameTemp, createAsSql});
                }
            }
            List<String> updateSelectSqlList = getUpdateSelectSqlText(text, tableNameTemp);
            if (!updateSelectSqlList.isEmpty()) {
                for (String s : updateSelectSqlList) {
                    sqlList.add(new String[]{tableNameTemp, s});
                }
            }
        }
        return sqlList;
    }

    public static String getCreateSqlText(String text, String tableNameTemp) {
        tableNameTemp = getFinalTableName(text, tableNameTemp);
        if(!text.contains("CREATE TABLE " + tableNameTemp + " (") && !text.contains("CREATE TABLE " + tableNameTemp + "(")) {
            return null;
        }
        if(text.indexOf("CREATE TABLE " + tableNameTemp + " (") < text.lastIndexOf("CREATE TABLE " + tableNameTemp + " (")
          || text.indexOf("CREATE TABLE " + tableNameTemp + "(") < text.lastIndexOf("CREATE TABLE " + tableNameTemp + "(")) {
            System.err.println("有多个CREATE TABLE " + tableNameTemp + " (");
            return null;
        }
        String[] texcArr = text.split(";");
        for (String textPart : texcArr) {
            String textIrim = textPart.trim();
            if (!textIrim.contains("CREATE TABLE " + tableNameTemp + "(") && !textIrim.contains("CREATE TABLE " + tableNameTemp + " (")) {
                continue;
            }
            if (textIrim.contains(" SELECT ")) {
                continue;
            }
            return textIrim;
        }
        return null;
    }

    public static boolean matchBracket(String text) {
        return matchBracket(text, "(", ")");
    }

    public static boolean notMatchBracket(String[] parts) {
        return notMatchBracket(parts, "(", ")");
    }

    public static String replaceFunc(String middle) {
        String[] parts = middle.split(",");
        while (notMatchBracket(parts)) {
            StringBuilder stringBuilder = new StringBuilder();
            for (String part : parts) {
                if(getCharCount(part, "(") <= getCharCount(part, ")")) {
                    stringBuilder.append(part).append(",");
                } else {
                    stringBuilder.append(part).append("|");
                }
            }
            middle = stringBuilder.substring(0, stringBuilder.length() - 1);
            parts = middle.split(",");
        }
        return middle;
    }

    public static String getInsertFields(String selectColumns) {
        List<String> list = new ArrayList<>();
        for (String sColumn : selectColumns.split(",")) {
            String trim = substringByLastSymbol(sColumn, " ", ".");
            list.add(trim);
        }
        return String.join(",", list);
    }

    public static String getFinalTableName(String text, String tableName) {
        while (text.contains("CREATE TABLE " + tableName + " LIKE ")) {
            String s = "CREATE TABLE " + tableName + " LIKE ";
            String right = text.substring(text.indexOf(s) + s.length()).trim();
            tableName = substringBySymbol(right, " ", ";");
        }
        return tableName;
    }

    public static boolean isUpdateSQL(String sqlText, String table) {
        if (!sqlText.startsWith("UPDATE " + table + " ") && !sqlText.startsWith("UPDATE " + table + ",")) {
            return false;
        }
        if (!sqlText.contains(" SET ")) {
            return false;
        }
        String left = sqlText.substring(0, sqlText.indexOf(" SET "));
        if (left.contains("SELECT ")) {
            left = left.substring(0, left.indexOf("SELECT "));
        }
        return left.contains(",") || left.contains(" JOIN ");
    }

    public static boolean isTempTable(String text, String table) {
        return text.contains("CREATE TABLE " + table + " ") || text.contains("CREATE TABLE " + table + "(");
    }

    public static List<String> getUpdateSqlText(String text, String tableTemp) {
        List<String> list = new ArrayList<>();
        if(!text.contains("UPDATE ")) {
            return list;
        }
        String[] textArr = text.split(";");
        for (String textPart : textArr) {
            String textTrim = textPart.trim();
            if (isUpdateSQL(textTrim, tableTemp)) {
                list.add(textTrim);
            }
        }
        return list;
    }

    public static Set<Map<String,String>> getTargetTable(TransformVO transformVO) {
        Set<Map<String,String>> set = new HashSet<>();
        String text = transformVO.getText();
        String[] textArr = transformVO.getTextArr();
        Set<String> tableList = getTempTableList(text);
        l: for (String tab : tableList) {
            if(isBlank(tab) || tab.contains("_MANUAL_S_")) {
                continue;
            }
            for (String textPart : textArr) {
                if(textPart.startsWith("ALTER TABLE " + tab + " RENAME TO ")) {
                    String tableName = textPart.substring(("ALTER TABLE " + tab + " RENAME TO ").length());
                    Map<String,String> map = new HashMap<>();
                    map.put("tableName", tableName);
                    map.put("tableNameTemp", tab);
                    set.add(map);
                }
            }
            for (String textPart : textArr) {
                if(textPart.startsWith("CREATE TABLE " + tab + " ") || textPart.startsWith("CREATE TABLE " + tab + "(")) {
                    continue l;
                }
            }
            for (String textPart : textArr) {
                if(textPart.startsWith("INSERT INTO " + tab + " ") || textPart.startsWith("INSERT INTO " + tab + "(") || isUpdateSQL(textPart, tab)) {
                    Map<String,String> map = new HashMap<>();
                    map.put("tableName", tab);
                    map.put("tableNameTemp", tab);
                    set.add(map);
                    break;
                }
            }
        }
        return set;
    }

    public static boolean contains(String text, String word) {
        return contains(text, word, " ", ",", ".", "(", ")", "|", "=");
    }

    public static boolean containsLeft(String text, String word, String... separators) {
        if(!text.contains(word)) {
            return false;
        }
        if(text.endsWith(word)) {
            return true;
        }
        for (String separator : separators) {
            if(text.contains(word + separator)) {
                return true;
            }
        }
        return false;
    }

    public static List<String> getUnienSelectList(String sqlText, boolean checkBracket) {
        List<String> list = new ArrayList<>();
        if(!sqlText.contains(" UNION ALL ")) {
            list.add(sqlText);
            return list;
        }
        String[] sqlTextArr = sqlText.split(" UNION ALL ");
        list.addAll(Arrays.asList(sqlTextArr));
        if(checkBracket) {
            for (String selectText : sqlTextArr) {
                if(!matchBracket(selectText)) {
                    System.err.println("!matchBracket(selectText) : " + formatSql(selectText));
                }
            }
        }
        return list;
    }

    public static List<Map<String, String>> getColumnsInfoByCreateAsText(String text, String tableNameTemp) {
        List<Map<String, String>> list = new ArrayList<>();
        String createText = getCreateAsSqlText(text, tableNameTemp);
        if(isBlank(createText)) {
            return list;
        }
        if(hasStarExpression(createText)) {
            return list;
        }
        createText = substringBySymbol(createText, " UNION ALL ");
        String selectColumns = createText.substring(createText.indexOf(" SELECT ") + " SELECT ".length(), createText.indexOf(" FROM "));
        if(isBlank(selectColumns)) {
            return list;
        }
        String insertFields = getInsertFields(selectColumns);
        if(isBlank(insertFields)) {
            return list;
        }
        String[] parts = insertFields.split(",");
        for (String part : parts) {
            if("".equals(part) || "*".equals(part)) {
                continue;
            }
            Map<String, String> map = new HashMap<>();
            map.put("TABLE_NAME", tableNameTemp);
            map.put("COLUMN_NAME", part);
            list.add(map);
        }
        return list;
    }

    public static Set<String> getAllTableBySelect(String sqlText) {
        List<String> sqlList = getUnienSelectList(sqlText, false);
        Set<String> tableSet = new HashSet<>();
        for (String selectText : sqlList) {
            tableSet.addAll(getTableBySelect(selectText));
        }
        return tableSet;
    }

    public static Set<String> getTableBySelect(String sqlText) {
        Set<String> tableSet = new HashSet<>();
        String right = sqlText;
        if(sqlText.contains(" FROM ")) {
            right = sqlText.substring(sqlText.indexOf(" FROM ") + " FROM ".length());
        }
        if(!right.contains(" JOIN ")) {
            if(right.contains(" WHERE ")) {
                String subText = right.substring(0, right.indexOf(" WHERE "));
                if(subText.contains(",")) {
                    String[] subTextArr = subText.split(",");
                    for (String tableName : subTextArr) {
                        tableName = substringBySymbol(tableName, " ", ")");
                        tableSet.add(tableName);
                    }
                }  else {
                    tableSet.add(substringBySymbol(subText, " ", ")"));
                }
            } else {
                tableSet.add(substringBySymbol(right, " ", ")"));
            }
        } else {
            String[] rightPartArr = right.split(" JOIN ");
            for (String rightPart : rightPartArr) {
                rightPart = substringBySymbol(rightPart, " ", ")");
                tableSet.add(rightPart);
            }
        }
        return tableSet;
    }

    public static String getUpdateTable(String sqlText) {
        if(sqlText.startsWith("UPDATE ") && sqlText.contains(" SET ")) {
            String sqlText1 = sqlText.substring(0, sqlText.indexOf(" SET "));
            a: if(sqlText1.contains(",")) {
                String left = sqlText1.substring(0, sqlText1.indexOf(","));
                if(left.contains(" JOIN ")) {
                    break a;
                }
                return substringBySymbol(sqlText1.substring(sqlText1.indexOf(",") + ",".length()), " ");
            }
            b: if(sqlText1.contains(" JOIN ")) {
                String left = sqlText1.substring(0, sqlText1.indexOf(" JOIN "));
                if(left.contains(",")) {
                    break b;
                }
                return substringBySymbol(sqlText1.substring(sqlText1.indexOf(" JOIN ") + " JOIN ".length()), " ");
            }
        }
        return null;
    }

    public static boolean hasStarExpression(String text) {
        return text.contains("SELECT * FROM ") || text.contains("SELECT *,") || text.contains(",* FROM ")
                || text.contains(".* FROM ") || text.contains(".*,");
    }

    public static String getColumns(List<Map<String, String>> columns) {
        StringBuilder stringBuilder = new StringBuilder();
        for (Map<String, String> map : columns) {
            stringBuilder.append(map.get("COLUMN_NAME")).append(",");
        }
        if(stringBuilder.length() > 0) {
            return stringBuilder.substring(0, stringBuilder.length() - 1);
        }
        return null;
    }

    public static List<Map<String, String>> getColumnsInfoByCreateText(String text, String tableNameTemp) {
        List<Map<String, String>> list = new ArrayList<>();
        String createText = getCreateSqlText(text, tableNameTemp);
        if(isBlank(createText)) {
            return list;
        }
        createText = substringBySymbol(createText, " BY ", " BY(");
        createText = createText.substring(createText.indexOf("(") + 1, createText.lastIndexOf(")"));
        String[] cols = createText.split(",");
        for (String col : cols) {
            if(col.startsWith("KEY ") || col.startsWith("PRIMARY KEY")) {
                continue;
            }
            int index = col.trim().indexOf(" ");
            if(index == -1) {
                continue;
            }
            String columnName = col.trim().substring(0, index);
            Map<String, String> map = new HashMap<>();
            map.put("TABLE_NAME", tableNameTemp);
            map.put("COLUMN_NAME", columnName);
            list.add(map);
        }
        return list;
    }

    public static String getReplacedSimpleText(String text) {
        text = replaceChar(text, "|", "<", ">", "!", "+", "-", "*", "/", "%");
        return trimSpace(text, "|");
    }

    public static String formatSql(String text) {
        String[] sqlArr = text.split(";");
        StringBuilder stringBuilder1 = new StringBuilder();
        for (String sql : sqlArr) {
            sql = sql.trim();
            if(sql.startsWith("CREATE TABLE ") || sql.startsWith("INSERT INTO ") || sql.startsWith("UPDATE ")) {
                sql = sql.replace(" JOIN ", " INNER JOIN ")
                        .replace(" INNER INNER JOIN ", " INNER JOIN ")
                        .replace(" LEFT INNER JOIN ", " LEFT JOIN ")
                        .replace(" RIGHT INNER JOIN ", " RIGHT JOIN ")
                        .replace(" FULL INNER JOIN ", " FULL JOIN ");
                String[] seperators = {" FROM ", " INNER JOIN ", " LEFT JOIN ", " RIGHT JOIN ", " FULL JOIN ",
                        " SELECT ", " WHERE ", " GROUP BY "};
                for (String seperator : seperators) {
                    sql = sql.replace(seperator, "\n" + seperator.substring(1));
                }
                if(sql.contains(" UNION ALL\n")) {
                    sql = sql.replace(" UNION ALL\n", "\nUNION ALL\n");
                }
                StringBuilder stringBuilder = new StringBuilder();
                for (String line : sql.split("\n")) {
                    if(line.startsWith("CREATE TABLE ") || line.startsWith("INSERT INTO ") || line.startsWith("UPDATE ")
                            || line.startsWith("FROM ") || line.startsWith("SELECT ") || line.startsWith("WHERE ") || line.startsWith("UNION ALL")
                            || line.startsWith("INNER JOIN ") || line.startsWith("LEFT JOIN ") || line.startsWith("RIGHT JOIN ") || line.startsWith("FULL JOIN ")
                            || line.startsWith("GROUP BY ") || line.startsWith("HAVING ")) {
                        stringBuilder.append("    ").append(line).append("\n");
                    } else {
                        stringBuilder.append("        ").append(line).append("\n");
                    }
                }
                stringBuilder1.append(stringBuilder).append("    ;\n");
            } else {
                stringBuilder1.append("    ").append(sql).append(";\n");
            }
        }
        return stringBuilder1.toString();
    }
}
